/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package daos;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import models.*;
import util.WebConst;

/**
 *
 * @author trietto
 */
public class OrderDAO {

    private PreparedStatement pStmtSelectCount = null;
    private PreparedStatement pStmtSelectCountPerMember = null;
    private PreparedStatement pStmtSelectAll = null;
    private PreparedStatement pStmtSelectAllPerMember = null;
    private PreparedStatement pStmtSelect = null;
    private PreparedStatement pStmtInsert = null;
    private PreparedStatement pStmtDelete = null;
    private PreparedStatement pStmtUpdate = null;
    private static OrderDAO orderDAO = null;
    SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy");

    public static OrderDAO singleton() {
        if (orderDAO == null) {
            orderDAO = new OrderDAO();
        }
        return orderDAO;
    }

    public OrderDAO() {
        try {
            pStmtSelectCount = DBConnection.getConnection().prepareStatement("SELECT Count(OrderId) FROM Orders");
            pStmtSelectCountPerMember = DBConnection.getConnection().prepareStatement("SELECT Count(OrderId) FROM Orders WHERE memberId=?");
            pStmtSelectAll = DBConnection.getConnection().prepareStatement("SELECT * FROM Orders LIMIT ?,?");
            pStmtSelectAllPerMember = DBConnection.getConnection().prepareStatement("SELECT * FROM Orders WHERE memberId=? LIMIT ?,?");
            pStmtSelect = DBConnection.getConnection().prepareStatement("SELECT * FROM Orders WHERE OrderId = ?");
            pStmtInsert = DBConnection.getConnection().prepareStatement("INSERT INTO Orders(OrderId, MemberId"
                    + ",OrderDate,Payment,Shipping) VALUES(?,?,?,?,?)");
            pStmtDelete = DBConnection.getConnection().prepareStatement("DELETE FROM Orders WHERE OrderId = ?");
            pStmtUpdate = DBConnection.getConnection().prepareStatement("UPDATE Orders SET "
                    + "MemberId=?,OrderDate=?,Payment=?,Shipping=? WHERE OrderId = ?");
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int recordCount() {
        int totalRecord = 0;
        try {            
            ResultSet rs = pStmtSelectCount.executeQuery();
            while (rs.next()) {
                totalRecord = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(CardTypeDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return totalRecord;
    }

    public int recordCount(int memberId) {
        int totalRecord = 0;
        try {
            pStmtSelectCountPerMember.setInt(1, memberId);
            ResultSet rs = pStmtSelectCountPerMember.executeQuery();
            while (rs.next()) {
                totalRecord = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(CardTypeDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return totalRecord;
    }

    public ArrayList<Order> selectAll(int page) {
        ArrayList<Order> lstOrders = new ArrayList<Order>();
        int offset = (page - 1) * WebConst.ROW_PER_PAGE;
        try {
            pStmtSelectAll.setInt(1, offset);
            pStmtSelectAll.setInt(2, WebConst.ROW_PER_PAGE);
            ResultSet rs = pStmtSelectAll.executeQuery();

            while (rs.next()) {
                lstOrders.add(new Order(rs.getString(1), rs.getInt(2),
                        sdf.parse(rs.getString(3)), rs.getString(4), rs.getString(5)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception e) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, e);
        }
        return lstOrders;
    }

    public ArrayList<Order> selectAllPerMember(int page, int memberId) {
        ArrayList<Order> lstOrders = new ArrayList<Order>();
        int offset = (page - 1) * WebConst.ROW_PER_PAGE;
        try {
            pStmtSelectAllPerMember.setInt(1, memberId);
            pStmtSelectAllPerMember.setInt(2, offset);
            pStmtSelectAllPerMember.setInt(3, WebConst.ROW_PER_PAGE);
            ResultSet rs = pStmtSelectAllPerMember.executeQuery();

            while (rs.next()) {
                lstOrders.add(new Order(rs.getString(1), rs.getInt(2),
                        sdf.parse(rs.getString(3)), rs.getString(4), rs.getString(5)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception e) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, e);
        }
        return lstOrders;
    }

    public Order select(String orderId) {
        Order order = null;
        try {
            pStmtSelect.setString(1, orderId);
            ResultSet rs = pStmtSelect.executeQuery();
            while (rs.next()) {
                order = new Order(rs.getString(1), rs.getInt(2),
                        sdf.parse(rs.getString(3)), rs.getString(4), rs.getString(5));
            }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception e) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, e);
        }
        return order;
    }

    public String insert(Order order) {
        String result = "";
        boolean bRet = false;
        try {
            pStmtInsert.setString(1, order.getOrderId());
            pStmtInsert.setInt(2, order.getMemberId());
            pStmtInsert.setDate(3, new java.sql.Date(order.getOrderDate().getTime()));
            pStmtInsert.setString(4, order.getPayment());
            pStmtInsert.setString(5, order.getShipping());

            pStmtInsert.execute();
            bRet = true;
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = bRet ? "Successed" : "Failed";
        return result;
    }

    public String delete(String orderId) {
        String result = "";
        int numOfRows = 0;
        try {
            pStmtDelete.setString(1, orderId);
            numOfRows = pStmtDelete.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = numOfRows > 0 ? "Successed" : "Failed";

        return result;
    }

    public String update(Order order) {
        String result = "";
        int numOfRows = 0;
        try {
            pStmtUpdate.setInt(1, order.getMemberId());
            pStmtUpdate.setDate(2, new java.sql.Date(order.getOrderDate().getTime()));
            pStmtUpdate.setString(3, order.getPayment());
            pStmtUpdate.setString(4, order.getShipping());
            pStmtUpdate.setString(5, order.getOrderId());
            numOfRows = pStmtUpdate.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(OrderDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = numOfRows > 0 ? "Successed" : "Failed";

        return result;
    }
}
